/**
 * Copyright(C) 2012 Luvina
 * TblUserDaoImpl.java, 25-05-2012 NguyenVietDung
 */
package net.luvina.manageuser.dao.impl;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import net.luvina.manageuser.dao.TblUserDao;
import net.luvina.manageuser.dto.TestDto;
import net.luvina.manageuser.dto.UserDetailDto;
import net.luvina.manageuser.hibernate.TblUser;
import net.luvina.manageuser.hibernate.User;

import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.jgroups.protocols.LOSS;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.stereotype.Repository;

import com.mysql.jdbc.PreparedStatement;

/**
 *
 * TblUserDaoImpl
 *
 * @author NguyenVietDung
 */
@Repository("tblUserDao")
public class TblUserDaoImpl extends GenericDaoImpl<TblUser, Integer> implements TblUserDao {

	/**
	 * @param type
	 */
	public TblUserDaoImpl() {
		super(TblUser.class);
		// TODO Auto-generated constructor stub
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see
	 * net.luvina.manageuser.dao.TblUserDao#checkExistedLoginName(java.lang.
	 * Integer, java.lang.String)
	 */
	@Override
	public TblUser getUserByLoginName(final Integer userId, final String loginName) {
		HibernateCallback callback = new HibernateCallback() {
			/*
			 * (non-Javadoc)
			 *
			 * @see
			 * org.springframework.orm.hibernate3.HibernateCallback#doInHibernate
			 * (org.hibernate.Session)
			 */
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				StringBuilder sqlCommand = new StringBuilder();
				// SELECT statement
				sqlCommand.append("FROM TblUser as u ");
				sqlCommand.append(" INNER JOIN FETCH u.mstGroup ug ");
				// WHERE statement
				if (userId == 0) {
					sqlCommand.append(" WHERE u.loginName=:loginName ");
				} else if (userId > 0) {
					sqlCommand.append(" WHERE u.userId=:userId ");
					sqlCommand.append(" AND u.loginName!=:loginName");
				}
				Query query = session.createQuery(sqlCommand.toString());
				if (userId == 0) {
					query.setString("loginName", loginName);
				} else if (userId > 0) {
					query.setString("userId", String.valueOf(userId));
					query.setString("loginName", loginName);
				}
				List<TblUser> lsTblUser = query.list();
				TblUser tblUser = new TblUser();
				if (lsTblUser.size() > 0) {
					tblUser = lsTblUser.get(0);
				} else {
					tblUser = null;
				}
				return tblUser;
			}
		};
		return getHibernateTemplate().execute(callback);
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see
	 * net.luvina.manageuser.dao.TblUserDao#getDetailUser(java.lang.Integer)
	 */
	@Override
	public UserDetailDto getDetailUser(Integer userId) {
		// TODO Auto-generated method stub
		return null;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see net.luvina.manageuser.dao.TblUserDao#getListUser(int, int, int,
	 * java.lang.String, java.lang.String, java.lang.String, java.lang.String,
	 * java.lang.String)
	 */
	@Override
	public List<UserDetailDto> getListUser(final int offset, final int limit, final int groupId, final String fullName, final String sortType,
			final String sortByFullName, final String sortByCodeLevel, final String sortByEndDate) {
		// TODO Auto-generated method stub
		final HibernateCallback callBack = new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException {
				// TODO Auto-generated method stub
				StringBuilder sqlCommand = new StringBuilder();
				// Create native sql command
				// Do not select *, plz select what you want
				// Select statement
				sqlCommand = sqlCommand.append("SELECT ");
				sqlCommand = sqlCommand.append("u.user_id as userID, ");
				sqlCommand = sqlCommand.append("u.full_name as fullname, ");
				sqlCommand = sqlCommand.append("u.birthday as birthday, ");
				sqlCommand = sqlCommand.append("g.group_name as groupName, ");
				sqlCommand = sqlCommand.append("u.email as userEmail, ");
				sqlCommand = sqlCommand.append("u.tel as userTel, ");
				sqlCommand = sqlCommand.append("mstj.name_level as nameLevel, ");
				sqlCommand = sqlCommand.append("tduj.end_date as endDate, ");
				sqlCommand = sqlCommand.append("tduj.total as total ");
				// From statement
				sqlCommand = sqlCommand.append("FROM tbl_user as u ");
				sqlCommand = sqlCommand.append("INNER JOIN mst_group as g ");
				sqlCommand = sqlCommand.append("ON u.group_id = g.group_id ");
				sqlCommand = sqlCommand.append("LEFT JOIN tbl_detail_user_japan as tduj ");
				sqlCommand = sqlCommand.append("ON u.user_id = tduj.user_id ");
				sqlCommand = sqlCommand.append("LEFT JOIN mst_japan as mstj ");
				sqlCommand = sqlCommand.append("ON tduj.code_level = mstj.code_level");
				// Where statement
				if (groupId > 0 && (fullName != null && fullName != "")) {
					sqlCommand.append(" WHERE g.group_id=:group_id");
					sqlCommand.append(" AND u.full_name LIKE :full_name");
				} else {
					if (fullName != null && fullName != "") {
						sqlCommand.append(" WHERE u.full_name LIKE :full_name");
					}
					if (groupId > 0) {
						sqlCommand.append(" WHERE g.group_id=:group_id");
					}
				}
				// ORDER BY STATEMENT
				if ("full_name".equalsIgnoreCase(sortType)) {
					sqlCommand = sqlCommand.append(" ORDER BY u." + sortType + " " + sortByFullName);
					sqlCommand = sqlCommand.append(", tduj.code_level " + sortByCodeLevel);
					sqlCommand = sqlCommand.append(", tduj.end_date " + sortByEndDate);
				}
				if ("code_level".equalsIgnoreCase(sortType)) {
					sqlCommand = sqlCommand.append(" ORDER BY tduj." + sortType + " " + sortByCodeLevel);
					sqlCommand = sqlCommand.append(", u.full_name " + sortByFullName);
					sqlCommand = sqlCommand.append(", tduj.end_date " + sortByEndDate);
				}
				if ("end_date".equalsIgnoreCase(sortType)) {
					sqlCommand = sqlCommand.append(" ORDER BY tduj.end_date " + sortByEndDate);
					sqlCommand = sqlCommand.append(", u.full_name " + sortByFullName);
					sqlCommand = sqlCommand.append(", tduj.code_level  " + sortByCodeLevel);
				}

				// Execute native Sql, return HSQL object: beanDTO
				Query query = session.createSQLQuery(sqlCommand.toString()).addScalar("userID", Hibernate.INTEGER).addScalar("fullName",
						Hibernate.STRING).addScalar("birthday", Hibernate.DATE).addScalar("groupName", Hibernate.STRING).addScalar("userEmail",
						Hibernate.STRING).addScalar("userTel", Hibernate.STRING).addScalar("nameLevel", Hibernate.STRING).addScalar("total",
						Hibernate.INTEGER).addScalar("endDate", Hibernate.DATE).setResultTransformer(Transformers.aliasToBean(UserDetailDto.class));
				// Select results range
				query.setFirstResult(offset);
				query.setMaxResults(limit);
				// Fill value to var in sqlCommand
				if (groupId > 0 && (fullName != null && fullName != "")) {
					query.setString("group_id", String.valueOf(groupId));
					query.setString("full_name", "%" + fullName + "%");
				} else {
					if (fullName != null && fullName != "") {
						query.setString("full_name", "%" + fullName + "%");
					}
					if (groupId > 0) {
						query.setString("group_id", String.valueOf(groupId));
					}
				}
				return (List<UserDetailDto>) query.list();
			}
		};
		return (List<UserDetailDto>) getHibernateTemplate().execute(callBack);
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see net.luvina.manageuser.dao.TblUserDao#getTotalRecords(int,
	 * java.lang.String)
	 */
	@Override
	public int getTotalRecords(final int groupId, final String fullName) {
		// TODO Auto-generated method stub
		HibernateCallback callback = new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				StringBuilder sqlCommand = new StringBuilder();
				sqlCommand.append(" FROM TblUser u ");
				sqlCommand.append(" INNER JOIN FETCH u.mstGroup ug ");
				// Where statement
				if (groupId > 0 && (fullName != null && fullName != "")) {
					sqlCommand.append(" WHERE ug.groupId=:groupId");
					sqlCommand.append(" AND u.fullName LIKE :fullName");
				} else {
					if (fullName != null && fullName != "") {
						sqlCommand.append(" WHERE u.fullName LIKE :fullName");
					}
					if (groupId > 0) {
						sqlCommand.append(" WHERE ug.groupId=:groupId");
					}
				}
				// Create queryString
				Query query = session.createQuery(sqlCommand.toString());
				// Fill value to var in sqlCommand
				if (groupId > 0 && (fullName != null && fullName != "")) {
					query.setString("groupId", String.valueOf(groupId));
					query.setString("fullName", "%" + fullName + "%");
				} else {
					if (fullName != null && fullName != "") {
						query.setString("fullName", "%" + fullName + "%");
					}
					if (groupId > 0) {
						query.setString("groupId", String.valueOf(groupId));
					}
				}

				List<TblUser> lsTblUser = query.list();
				if (lsTblUser != null && lsTblUser.size() > 0) {
					return lsTblUser.size();
				} else {
					return 0;
				}
			}
		};
		return (Integer) getHibernateTemplate().execute(callback);
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see
	 * net.luvina.manageuser.dao.TblUserDao#getUserByEmail(java.lang.Integer,
	 * java.lang.String)
	 */
	@Override
	public TblUser getUserByEmail(final Integer userId, final String email) {
		HibernateCallback callback = new HibernateCallback() {
			/*
			 * (non-Javadoc)
			 *
			 * @see
			 * org.springframework.orm.hibernate3.HibernateCallback#doInHibernate
			 * (org.hibernate.Session)
			 */
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				StringBuilder sqlCommand = new StringBuilder();
				// SELECT statement
				sqlCommand.append("FROM TblUser as u ");
				sqlCommand.append(" INNER JOIN FETCH u.mstGroup ug ");
				// WHERE statement
				if (userId == 0) {
					sqlCommand.append(" WHERE u.email=:email ");
				} else if (userId > 0) {
					sqlCommand.append(" WHERE u.userId=:userId ");
					sqlCommand.append(" AND u.email!=:email");
				}
				Query query = session.createQuery(sqlCommand.toString());
				if (userId == 0) {
					query.setString("email", email);
				} else if (userId > 0) {
					query.setString("userId", String.valueOf(userId));
					query.setString("email", email);
				}
				List<TblUser> lsTblUser = query.list();
				TblUser tblUser = new TblUser();
				if (lsTblUser.size() > 0) {
					tblUser = lsTblUser.get(0);
				} else {
					tblUser = null;
				}

				return tblUser;
			}
		};
		return getHibernateTemplate().execute(callback);
	}
}
